/*DONE* Table [dbo].<CompanyName>$POS Terminal$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 29.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$POS Terminal$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$POS Terminal$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](10) NOT NULL,
	[Store No_] [nvarchar](10) NOT NULL,
	[Terminal Type] [int] NOT NULL,
	[Description] [nvarchar](30) NOT NULL,
	[Placement] [nvarchar](30) NOT NULL,
	[Statement Method] [int] NOT NULL,
	[Terminal Statement] [tinyint] NOT NULL,
	[Default Price Group] [nvarchar](10) NOT NULL,
	[Not Active] [tinyint] NOT NULL,
	[Terminal Network ID] [nvarchar](20) NOT NULL,
	[Terminal IP Address] [nvarchar](15) NOT NULL,
	[Terminal Connection] [int] NOT NULL,
	[Show Item Image] [tinyint] NOT NULL,
	[Show Item Html] [tinyint] NOT NULL,
	[Display Terminal Closed] [tinyint] NOT NULL,
	[Display Linked Item] [tinyint] NOT NULL,
	[Fraud Sort Field] [decimal](38, 20) NOT NULL,
	[Manager Key on Return] [tinyint] NOT NULL,
	[KeyLock Staff Perm_ Group] [nvarchar](10) NOT NULL,
	[Slip if Return] [int] NOT NULL,
	[Open Drawer at LI_LO] [tinyint] NOT NULL,
	[Only Total in Susp_ Trans_] [tinyint] NOT NULL,
	[Exit After Each Trans_] [tinyint] NOT NULL,
	[AutoLogoff After (Min_)] [int] NOT NULL,
	[Allow AutoLogoff in Sales Mode] [tinyint] NOT NULL,
	[Return in Transaction] [tinyint] NOT NULL,
	[Item No_ on Receipt] [int] NOT NULL,
	[Print Receipt for Neg_ Adj_] [tinyint] NOT NULL,
	[EFT Embedded Receipt] [tinyint] NOT NULL,
	[EFT Service provider] [int] NOT NULL,
	[EFT Store No_] [nvarchar](100) NOT NULL,
	[EFT POS Terminal No_] [nvarchar](10) NOT NULL,
	[EFT Server] [nvarchar](30) NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Print Receipt Logo] [nvarchar](80) NOT NULL,
	[Print Receipt Bitmap No_] [int] NOT NULL,
	[Rcpt_ Text Max_ Length] [int] NOT NULL,
	[No_ of Top_Bottom Lines] [int] NOT NULL,
	[Receipt Barcode] [tinyint] NOT NULL,
	[Receipt Setup Location] [int] NOT NULL,
	[Display Text Max_ Length] [int] NOT NULL,
	[Customer Display Text 1] [nvarchar](40) NOT NULL,
	[Customer Display Text 2] [nvarchar](40) NOT NULL,
	[Print Receipt BC Type] [int] NOT NULL,
	[Receipt Barcode Width] [int] NOT NULL,
	[Receipt Barcode Height] [int] NOT NULL,
	[Online Trans_ Backup] [tinyint] NOT NULL,
	[Backup Database] [nvarchar](10) NOT NULL,
	[Sum of Trans_ No_] [bigint] NOT NULL,
	[Count of Trans_] [int] NOT NULL,
	[No_ Series] [nvarchar](10) NOT NULL,
	[Table Group] [nvarchar](10) NOT NULL,
	[Table View at Logon] [tinyint] NOT NULL,
	[Operational Mode] [int] NOT NULL,
	[Rec_ Printing Off by Default] [tinyint] NOT NULL,
	[Default Sales Type] [nvarchar](20) NOT NULL,
	[Safe No_] [nvarchar](10) NOT NULL,
	[Staff Login Validation] [int] NOT NULL,
	[Exclude from Cash Mgnt_] [tinyint] NOT NULL,
	[Last Z-Report] [nvarchar](10) NOT NULL,
	[Last Y-Report] [nvarchar](10) NOT NULL,
	[Hardware Profile] [nvarchar](10) NOT NULL,
	[Menu Profile] [nvarchar](10) NOT NULL,
	[Interface Profile] [nvarchar](10) NOT NULL,
	[Functionality Profile] [nvarchar](10) NOT NULL,
	[Style Profile] [nvarchar](20) NOT NULL,
	[Print VAT Refund Checks] [tinyint] NOT NULL,
	[Print Suspend with Prepayment] [tinyint] NOT NULL,
	[Print Number of Items] [tinyint] NOT NULL,
	[Print Total Savings] [tinyint] NOT NULL,
	[Print Discount Detail] [tinyint] NOT NULL,
	[Second Hardware Profile] [nvarchar](10) NOT NULL,
	[Print Second Receipt] [tinyint] NOT NULL,
	[Sales Type Filter] [nvarchar](250) NOT NULL,
	[Copy of Menu Profile] [nvarchar](10) NOT NULL,
	[Copy of Interface Profile] [nvarchar](10) NOT NULL,
	[Idle Timer Interval] [decimal](38, 20) NOT NULL,
	[External Printer ID] [nvarchar](10) NOT NULL,
	[Hosp_ Type Pop-up on Logon] [int] NOT NULL,
	[Receipt Barcode ID] [int] NOT NULL,
	[Printer KOT Status after Sent] [int] NOT NULL,
	[Sales Slip] [int] NOT NULL,
	[Only Email Sales Transactions] [tinyint] NOT NULL,
	[BCC E-Mail Address] [nvarchar](80) NOT NULL,
	[Inventory Main Menu] [nvarchar](10) NOT NULL,
	[Inventory No_ of Records] [int] NOT NULL,
	[Dual Disp_ Enabled] [tinyint] NOT NULL,
	[Dual Disp_ Style Profile] [nvarchar](20) NOT NULL,
	[Dual Disp_ Interface Profile] [nvarchar](20) NOT NULL,
	[Dual Disp_ Menu Profile] [nvarchar](20) NOT NULL,
	[Dual Disp_ Host] [nvarchar](30) NOT NULL,
	[Dual Disp_ Port] [int] NOT NULL,
	[Dual Disp_ Slideshow] [nvarchar](20) NOT NULL,
	[Forecourt Terminal ID] [nvarchar](10) NOT NULL,
	[Sales Order Nos_] [nvarchar](20) NOT NULL,
	[Item Filtering Method] [int] NOT NULL,
	[Show Numberpad] [tinyint] NOT NULL,
	[Device Type] [int] NOT NULL,
	[Device License Key] [nvarchar](80) NOT NULL,
	[Computer Name] [nvarchar](50) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Allow Post IM Documents] [tinyint] NOT NULL,
	[ASN Quantity Method] [int] NOT NULL,
 CONSTRAINT ['+@Company+'$POS Terminal$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$POS Terminal$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END